package com.zj.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;

import com.zj.bean.SexEnum;
import org.junit.Test;

/**
 * @ClassName: PreparedStatementTest
 * @Description: 通过PreparedStatement对象完成对数据库的CRUD操作
 * @author: 孤傲苍狼
 * @date: 2014-9-15 下午11:21:42
 */
public class PreparedStatementTest {

    @Test
    public void insert() {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            //获取一个数据库连接
            conn = JdbcUtils.getConnection();
            //要执行的SQL命令，SQL中的参数使用?作为占位符
            String sql = "insert into user_inf(name,sex,phoneNumber,birthday,address) values(?,?,?,?,?)";
            //通过conn对象获取负责执行SQL命令的prepareStatement对象
            st = conn.prepareStatement(sql);
            //为SQL语句中的参数赋值，注意，索引是从1开始的
            /**
             * SQL语句中各个字段的类型如下：
             *  +----------+-------------+
             | Field    | Type        |
             +----------+-------------+
             | id       | int(11)     |
             | name     | varchar(40) |
             | password | varchar(40) |
             | email    | varchar(60) |
             | birthday | date        |
             +----------+-------------+
             */
            st.setString(1, "神皇");//name是varchar(字符串类型)
            st.setInt(2, 1);//sex是int
            st.setString(3, "555587688");//
            st.setDate(4, new java.sql.Date(new Date().getTime()));//birthday是date类型
            st.setString(5, "乐山市6号");//

            //执行插入操作，executeUpdate方法返回成功的条数
            int num = st.executeUpdate();
            if (num > 0) {
                System.out.println("插入成功！！");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //SQL执行完成之后释放相关资源
            JdbcUtils.release(conn, st, rs);
        }
    }

    @Test
    public void delete() {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "delete from user_inf where id=?";
            st = conn.prepareStatement(sql);
            st.setInt(1, 40);
            int num = st.executeUpdate();
            if (num > 0) {
                System.out.println("删除成功！！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }
    }

    @Test
    public void update() {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "update user_inf set name=?,phoneNumber=? where id=?";
            st = conn.prepareStatement(sql);
            st.setString(1, "gacl");
            st.setString(2, "15645455336");
            st.setInt(3, 5);
            int num = st.executeUpdate();
            if (num > 0) {
                System.out.println("更新成功！！");
            }
        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            JdbcUtils.release(conn, st, rs);
        }
    }

    @Test
    public void find() {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        System.getProperties().put("sun.misc.ProxyGenerator.saveGeneratedFiles", "true");
        try {
            conn = JdbcPoolZJUtil.getConnection();
            String sql = "select * from user_inf where id=?";
            st = conn.prepareStatement(sql);
            st.setInt(1, 5);
            rs = st.executeQuery();
            if (rs.next()) {
                System.out.println(rs.getString("name"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcPoolZJUtil.release(conn, st, rs);
        }
    }
}
